--cambia la BD de la version 0.6 a la de la version 0.9

USE PIS;
GO

--se agrega el campo SisUrlWS a la tabla de sistemas
ALTER TABLE Sistemas
Add SisUrlWS varchar(256) NOT NULL CONSTRAINT DF_Sistemas_SisUrlWS DEFAULT ('Ingrese la url del web service de Sharepoint');	
GO

--se agrega el campo FechaSincronizacion a la tabla de usuarios
ALTER TABLE Usuarios
Add FechaSincronizacion datetime NOT NULL CONSTRAINT DF_Usuarios_FechaSincronizacion  DEFAULT (getdate())
GO

--se crean nuevos stored procedures y se borran los que cambiaron para volver a crearlos
DROP PROCEDURE AgregarSistema;
GO

DROP PROCEDURE ModificarSistema;
GO

DROP PROCEDURE ModificarUsuario;
GO

-------------------------------- SISTEMAS --------------------------------------
--Agrega un sistema en Sistemas y retorna 1 si lo puede agregar, 
--0 si ya existe uno con el mismo nombre para el mismo cliente 
--y -1 si NO lo puede agregar, -2 si no existe el cliente 
--y -3 si ya existe un sistema con ese sitio 
CREATE	PROCEDURE AgregarSistema	
			@SisNom varchar(40),
			@CliCod int,
			@SisSit varchar(256),
			@SisUrlWS varchar(256)
		AS
BEGIN
	--Debe de existir el cliente al cual se le va a agregar el sistema
	IF (NOT EXISTS (SELECT * FROM Clientes WHERE CliCod = @CliCod ))
		RETURN -2 
	ELSE  
		--No se puede repetir el nombre de sistema para el cliente
		IF(EXISTS (SELECT * FROM Sistemas WHERE SisNom=@SisNom AND CliCod=@CliCod))
			RETURN 0
	ELSE
		IF(EXISTS (SELECT * FROM Sistemas WHERE SisSit=@SisSit))
			RETURN -3
		ELSE
		BEGIN
			DECLARE @Error int
			DECLARE @Id int

			BEGIN TRAN
			INSERT Sistemas( SisNom, CliCod, SisSit, SisUrlWS ) VALUES( @SisNom, @CliCod, @SisSit, @SisUrlWS );
			SET @Error=@@ERROR;	
			SET @Id= @@Identity

			IF(@Error=0)
			BEGIN
				COMMIT TRAN;
				RETURN @Id;
			END
			ELSE
			BEGIN
				ROLLBACK TRAN;
				RETURN -1;
			END	
		END
END
GO





--Elimina un Sistema. Retorna 1 si lo puede eliminar 0 si no esta, -1 si NO lo puede eliminar por alguna razon, 
--y -2 si el sistema tiene WorkPackages y en este caso no lo elimina
CREATE PROCEDURE EliminarSistema 
			@SisCod int 
		AS
BEGIN 
	
	IF (NOT EXISTS (SELECT * FROM Sistemas WHERE SisCod = @SisCod ))
		RETURN 0
	ELSE IF (EXISTS (SELECT * FROM WorkPackages WHERE SisCod = @SisCod))
		RETURN -2
	BEGIN 

		DECLARE @Error int
		BEGIN TRAN
		DELETE Sistemas
		WHERE  SisCod = @SisCod
		SET @Error=@@ERROR
		IF(@Error=0)
		BEGIN
			COMMIT TRAN;
			RETURN 1
		END
		ELSE
		BEGIN
			ROLLBACK TRAN;
			RETURN -1
		END	
	END
END
GO


--Modifica el sistema de codigo @SisCod y retorna 1 si lo pudo hacer, 0 si no existe el cliente, -1 si NO lo pudo hacer, -2 si existe uno con ese nombre para el cliente
-- -3 si existe otro con el mismo sitio y -4 si no existe el cliente
CREATE PROCEDURE ModificarSistema
			@SisCod int,
			@SisNom varchar(40),
			@SisSit varchar(256),
			@SisUrlWS varchar(256),
			@CliCod int
		AS
BEGIN 
	IF (NOT EXISTS (SELECT * FROM Sistemas WHERE SisCod = @SisCod ))
		RETURN 0
	ELSE
	IF (NOT EXISTS (SELECT * FROM Clientes WHERE CliCod = @CliCod ))
		RETURN -4
	ELSE  
		--No se puede repetir el nombre de sistema para el cliente
		IF(EXISTS (SELECT * FROM Sistemas WHERE SisNom=@SisNom AND CliCod=@CliCod AND SisCod <> @SisCod))
			RETURN -2
		ELSE
		IF(EXISTS (SELECT * FROM Sistemas WHERE SisSit=@SisSit AND SisCod <> @SisCod))
			RETURN -3
		ELSE
	BEGIN
		UPDATE Sistemas
		SET SisNom = @SisNom, SisSit = @SisSit, SisUrlWS = @SisUrlWS, CliCod = @CliCod
		WHERE SisCod = @SisCod
		IF(@@Error=0)
			RETURN 1
		RETURN -1
	END
END
GO




-------------------------------- USUARIOS --------------------------------------
--Modifica el usuario de codigo @UsrCod y retorna 1 si lo pudo hacer, 0 si no existe el cliente, -1 si NO lo pudo hacer
--y -2 si ya existe un usuario con ese nombre.
CREATE PROCEDURE ModificarUsuario
			@UsrCod int,
			@UsrNom varchar(40),
			@FechaSincronizacion datetime
		AS
BEGIN 
	IF (NOT EXISTS (SELECT * FROM Usuarios WHERE UsrCod = @UsrCod ))
		RETURN 0
	ELSE IF(EXISTS (SELECT * FROM Usuarios WHERE UsrCod != @UsrCod AND UsrNom = @UsrNom))
		RETURN -2
	ELSE
	BEGIN
		UPDATE Usuarios
		SET UsrNom = @UsrNom, FechaSincronizacion = @FechaSincronizacion
		WHERE UsrCod = @UsrCod
		IF(@@Error=0)
			RETURN 1
		RETURN -1
	END
END
GO


--devuelve la persona con nombre UsrNom
CREATE PROCEDURE GetUsuarioPorNombre 
@UsrNom varchar(40) 
AS
BEGIN 
	SELECT *
	FROM Usuarios
	WHERE UsrNom = @UsrNom
END
GO

--devuelve la persona con codigo UsrCod
CREATE PROCEDURE GetUsuario 
@UsrCod int 
AS
BEGIN 
	SELECT *
	FROM Usuarios
	WHERE UsrCod = @UsrCod
END
GO



-------------------------------- WORK PACKAGES --------------------------------------
--Elimina un wprk package retorna 1 si lo puede eliminar 0 si no esta y -1 si NO lo puede eliminar por alguna razon
CREATE PROCEDURE EliminarWorkPackage 
			@WkpCod int 
		AS
BEGIN 
	
	IF (NOT EXISTS (SELECT * FROM WorkPackages WHERE WkpCod = @WkpCod ))
		RETURN 0
	ELSE
	BEGIN 

		DECLARE @Error int
		BEGIN TRAN
		DELETE WorkPackages
		WHERE  WkpCod = @WkpCod
		SET @Error=@@ERROR

		IF(@Error=0)
		BEGIN
			COMMIT TRAN;
			RETURN 1
		END
		ELSE
		BEGIN
			ROLLBACK TRAN;
			RETURN -1
		END	
	END
END
GO


--se crean los indices por clave foranea
CREATE INDEX FK_Sistemas_CliCod ON Sistemas(CliCod);
CREATE INDEX FK_WorkPackages_SisCod ON WorkPackages(SisCod);
CREATE INDEX FK_WorkPackages_WkpEstCod ON WorkPackages(WkpEstCod);
GO